
package org.epsgrau.DAO;

import java.util.List;
import org.epsgrau.TO.ActividadDetalleTO;
import org.epsgrau.TO.ActividadTO;
import org.epsgrau.TO.ProcesoTO;
import org.epsgrau.entity.ActividadDetalle;
import org.hibernate.Query;
import org.hibernate.Session;
import org.hibernate.SessionFactory;
import org.hibernate.transform.Transformers;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.orm.hibernate3.support.HibernateDaoSupport;
import org.springframework.stereotype.Repository;



@Repository
public class ActividadDetalleDAO extends HibernateDaoSupport implements ActividadDetalleDAOInterface {

   @Autowired
    public ActividadDetalleDAO(SessionFactory sessionFactory) {
        super.setSessionFactory(sessionFactory);
    }
        

    @Override
    public boolean insertarActualizaActividadDetalle(ActividadDetalle Ad) {
        boolean b=false;
        try{

        getHibernateTemplate().saveOrUpdate(Ad);
        b=true;
            
        }catch(Exception e){
            e.printStackTrace();
            
        }
        return b;
    }
   
   @Override
    public boolean eliminarActividadDetalle(String id) {
        boolean b=false;
        try{
            ActividadDetalle actividadDetalle = this.obtenerActividadDetallePorId(id);
            this.getHibernateTemplate().delete(actividadDetalle);
            b=true;
        }catch(Exception e){
        e.printStackTrace();
        }
        return b;
    }
    
   @Override
    public List<ProcesoTO> getListaProcesos() {
         Session session = getHibernateTemplate().getSessionFactory().openSession();
       
          Query consulta = session.createSQLQuery("select p.id_proceso as idproceso,"
                                            + "m.descripcion as descmateria,"
                                            + "p.nro_expediente as nroexpediente,"
                                            + "r.descripcion as descresponsable from public.\"proceso\" p "
                                            + "join public.\"materia\" m on p.id_materia=m.id_materia "
                                            + "join public.\"responsable\" r on p.id_responsable=r.id_responsable")
	        .setResultTransformer(Transformers.aliasToBean(ProcesoTO.class));
    
                return consulta.list();
    }

   

   @Override
   public List<ProcesoTO> getListaProcesosPorNroExpediente() {
         Session session = getHibernateTemplate().getSessionFactory().openSession();
       
          Query consulta = session.createSQLQuery("select id_proceso as idproceso,"
                                                + "nro_expediente as nroexpediente from public.proceso ")
	        .setResultTransformer(Transformers.aliasToBean(ProcesoTO.class));
    
                return consulta.list();
    }
   
   @Override
    public List<ActividadTO> getListaActividad() {
            Session session = getHibernateTemplate().getSessionFactory().openSession();
       
          Query consulta = session.createSQLQuery("select id_actividad as idactividad,"
                                            + "descripcion as descactividad from public.\"actividad\"")
	        .setResultTransformer(Transformers.aliasToBean(ActividadTO.class));
    
                return consulta.list();
    }

    
   @Override
    public List<ActividadDetalleTO> getListaActividadDetalle() {
            
     Session session = getHibernateTemplate().getSessionFactory().openSession();
         Query consulta = session.createSQLQuery("select ad.id_actividaddetalle as idactividaddetalle,"
                                           
                                            + " a.descripcion as idactividad,"
                                            + " CAST(ad.fecha_registro AS VARCHAR) as fecharegistro,"
                                            + " CAST(ad.fecha_notificacion AS VARCHAR) as fechanotificacion,"
                                            + " CAST(ad.fecha_realizacion AS VARCHAR) as fecharealizacion,"
                                            + " CAST(ad.fecha_limite  AS VARCHAR) as fechalimite,"
                                            + " ad.duracion as duracion,"
                                            + " p.nro_expediente as nroexpediente,"
                                            + " ad.observacion as observacion from public.\"actividad_detalle\" ad"
                                            + " left join public.\"proceso\" p on ad.id_proceso=p.id_proceso "
                 + "left join public.\"actividad\" a on ad.id_actividad=a.id_actividad")
	        .setResultTransformer(Transformers.aliasToBean(ActividadDetalleTO.class));
  
                
         
         return consulta.list();
    
    }
   
   @Override
   public List<ActividadDetalleTO> getListaActividadDetalleProceso(String id) {
            
     Session session = getHibernateTemplate().getSessionFactory().openSession();
         Query consulta = session.createSQLQuery("select ad.id_actividaddetalle as idactividaddetalle,"
                                            + " a.descripcion as idactividad,"
                                            + " CAST(ad.fecha_registro AS VARCHAR) as fecharegistro,"
                                            + " CAST(ad.fecha_notificacion AS VARCHAR) as fechanotificacion,"
                                            + " CAST(ad.fecha_realizacion AS VARCHAR) as fecharealizacion,"
                                            + " CAST(ad.fecha_limite  AS VARCHAR) as fechalimite,"
                                            + " ad.duracion as duracion,"
                                            + " p.nro_expediente as nroexpediente,"
                                            + " ad.observacion as observacion from public.\"actividad_detalle\" ad"
                                            + " inner join public.proceso p on ad.id_proceso=p.id_proceso "
                                            + " left join public.\"actividad\" a on ad.id_actividad=a.id_actividad "
                                            + " where ad.id_proceso='"+id+"'")
	        .setResultTransformer(Transformers.aliasToBean(ActividadDetalleTO.class));
  
                
         
         return consulta.list();
    
    }
    
   @Override
   public ActividadDetalle obtenerActividadDetallePorId(String id) {
       return (ActividadDetalle)this.getHibernateTemplate().get(ActividadDetalle.class, id);
    }
    
}
